package com.bridge.exceyexcel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.metadata.data.DataFormatData;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.SimpleColumnWidthStyleStrategy;
import com.sun.deploy.net.HttpResponse;
import com.sun.deploy.net.URLEncoder;
import org.apache.commons.compress.utils.Lists;
import org.apache.poi.ss.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.List;

public class EasyExcelUtils {


    public static void main(String[] args) throws IOException {

        EasyExcelUtils.easyExcelTest(null);
    }


    public static void easyExcelTest(HttpServletResponse response) throws IOException {
        List<List<String>> heads = Lists.newArrayList();
        for (int i = 1; i <= 20; i++) {
            List<String> list = new ArrayList<>();
            list.add("表头" + i);
            heads.add(list);
        }
        List<List<String>> contents = Lists.newArrayList();
        for (int i = 0; i <= 500000; i++) {
            List<String> content = Lists.newArrayList();
            for (int j = 0; j < 20; j++) {
                content.add("第" + i + "行第" + j + "例内容");
            }
            contents.add(content);
        }

        // 表头样式策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 设置数据格式
        DataFormatData formatData = new DataFormatData();
        formatData.setFormat("yyyy-MM-DD hh:mm:ss");
        headWriteCellStyle.setDataFormatData(formatData);
        // 是否换行
        headWriteCellStyle.setWrapped(false);
        // 水平对齐方式
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        // 垂直对齐方式
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 前景色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        // 背景色
        headWriteCellStyle.setFillBackgroundColor(IndexedColors.WHITE.getIndex());
        // 设置为1时，单元格将被前景色填充
        headWriteCellStyle.setFillPatternType(FillPatternType.NO_FILL);
        // 控制单元格是否应自动调整大小以适应文本过长时的大小
        headWriteCellStyle.setShrinkToFit(false);
        // 单元格边框类型
        headWriteCellStyle.setBorderBottom(BorderStyle.NONE);
        headWriteCellStyle.setBorderLeft(BorderStyle.NONE);
        headWriteCellStyle.setBorderRight(BorderStyle.NONE);
        headWriteCellStyle.setBorderTop(BorderStyle.NONE);
        // 单元格边框颜色
        headWriteCellStyle.setLeftBorderColor(IndexedColors.BLACK.index);
        headWriteCellStyle.setRightBorderColor(IndexedColors.BLACK.index);
        headWriteCellStyle.setTopBorderColor(IndexedColors.BLACK.index);
        headWriteCellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
        // 字体策略
        WriteFont writeFont = new WriteFont();
        // 是否加粗/黑体
        writeFont.setBold(false);
        // 字体颜色
        writeFont.setColor(Font.COLOR_NORMAL);
        // 字体名称
        writeFont.setFontName("宋体");
        // 字体大小
        writeFont.setFontHeightInPoints((short) 11);
        // 是否使用斜体
        writeFont.setItalic(false);
        // 是否在文本中使用横线删除
        writeFont.setStrikeout(false);
        // 设置要使用的文本下划线的类型
        writeFont.setUnderline(Font.U_NONE);
        headWriteCellStyle.setWriteFont(writeFont);

        // 内容样式策略策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.GENERAL);
        contentWriteCellStyle.setBorderBottom(BorderStyle.NONE);
        contentWriteCellStyle.setBorderLeft(BorderStyle.NONE);
        contentWriteCellStyle.setBorderRight(BorderStyle.NONE);
        contentWriteCellStyle.setBorderTop(BorderStyle.NONE);
        contentWriteCellStyle.setFillPatternType(FillPatternType.NO_FILL);
        contentWriteCellStyle.setWrapped(false);

//        EasyExcel.write("D:\\test.xlsx").head(heads)
//                .registerWriteHandler(new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle))
//                .registerWriteHandler(new SimpleColumnWidthStyleStrategy(16)) // 列宽
//                .sheet("销售订单").doWrite(contents);

        if(response == null){
            return;
        }

        // 通过接口调用，使用流生成文件
        String fileName = "导出测试";
        response.setCharacterEncoding("utf-8");
        fileName = URLEncoder.encode(fileName, "UTF-8") + ".xlsx";
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);
        response.setHeader("content-Type", "application/vnd.ms-excel");
        response.setHeader("filename", fileName);
        EasyExcel.write(response.getOutputStream()).head(heads)
//                .registerWriteHandler(new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle))
//                .registerWriteHandler(new SimpleColumnWidthStyleStrategy(16)) // 列宽
                .sheet("销售订单").doWrite(contents);

    }


    /**
     * 最简单的读
     * <p>1. 创建excel对应的实体对象 参照{@link DemoData}
     * <p>2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link DemoDataListener}
     * <p>3. 直接读即可
     */

//    public static void simpleRead(String path, String name) {
//        String fileName = getFileName(path, name);
//        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
//        EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
//    }
//
//    /**
//     * 最简单的写
//     * <p>1. 创建excel对应的实体对象 参照{@link com.alibaba.easyexcel.test.demo.write.DemoData}
//     * <p>2. 直接写即可
//     */
//    public static void simpleWrite(String path, String name) {
//        String fileName = getFileName(path, name);
//        // 这里 需要指定写用哪个class去读，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
//        // 如果这里想使用03 则 传入excelType参数即可
//        EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
//    }


    /**
     * 获取文件url
     *
     * @param path 路径
     * @param name 名称
     * @return
     */
    private static String getFileName(String path, String name) {
        return path + File.separator + name + (name.endsWith(".xlsx") ? name : name + ".xlsx");
    }
}
